package JDBCLEARN;//使用德鲁伊连接池
//将德鲁伊连接池封装成一个方法


import com.alibaba.druid.pool.DruidDataSourceFactory;
import org.junit.Test;

import javax.sql.DataSource;
import java.io.FileInputStream;
import java.io.IOException;
import java.sql.*;
import java.util.Properties;

public class JdbcLearnBackup4 {
    public static void main(String[] args) throws SQLException, ClassNotFoundException, IOException {
        JdbcLearn jdbcLearn = new JdbcLearn();
        jdbcLearn.insert();
    }

    //封装德鲁伊连接池方法
    public Connection link() throws Exception {
        //德鲁伊连接池
        Properties properties = new Properties();
        properties.load(new FileInputStream("src/JDBCLEARN/jdbc.properties"));
        DataSource dataSource = DruidDataSourceFactory.createDataSource(properties);
        Connection connection = dataSource.getConnection();
        System.out.println("connection = " + connection);
        return connection;
    }



    @Test
    public void insert() throws Exception {
        //使用封装的德鲁伊连接池方法
        Connection connection = link();


        String sql="insert into student values(null,'李同学999','女',18,'csgo')";

        //4.创建数据操作对象
        Statement statement = connection.createStatement();

        //5.执行sql，获取结果集
        int i = statement.executeUpdate(sql);
        System.out.println("向数据库中插入了i = " + i);

        //6. 关闭资源
        //关闭资源
        statement.close();
        connection.close();
    }
    /**
     * 数据的查询
     * @throws ClassNotFoundException
     * @throws SQLException
     */
    @Test
    public void selectData() throws Exception {

        //使用封装的德鲁伊连接池方法
        Connection connection = link();

        //3.实现Crud
        String sql = "select * from student";
        //4.创建数据操作对象
        PreparedStatement statement = connection.prepareStatement(sql);
        //5.执行sql，获取结果集
        ResultSet resultSet = statement.executeQuery();

        //6.通过next指向结果集第一行
        while (resultSet.next()) {
            //7.解析结果中数据字段
            System.out.println("resultSet.getInt(\"sid\") = " + resultSet.getInt("sid"));
            System.out.println("resultSet.getString(\"ssex\") = " + resultSet.getString("ssex"));
            System.out.println("resultSet.getInt(\"sage\") = " + resultSet.getString("sage"));
            System.out.println("--------------------------------");
        }
        //关闭资源
        statement.close();
        connection.close();

    }

    /**
     * 删除某条记录
     * @throws SQLException
     * @throws ClassNotFoundException
     */

    @Test
    public void deleteDb() throws Exception {

        //使用封装的德鲁伊连接池方法
        Connection connection = link();



//3.定义sql
//拼接
        String sql="delete from student where sname=?";

        System.out.println("sql = " + sql);


        //4.创建数据操作对象
        PreparedStatement statement = connection.prepareStatement(sql);
        statement.setString(1,"李同学999");

        //5.执行sql，获取结果集
        int i = statement.executeUpdate();
        System.out.println("删除数据库中 " + i);

        //6. 关闭资源
        //关闭资源
        statement.close();
        connection.close();
    }



       @Test
    public void inserttest() throws Exception {
           //使用封装的德鲁伊连接池方法
           Connection connection = link();
        String sql="insert into student values(null,?,?,?,?)";

        //4.创建数据操作对象
        PreparedStatement statement = connection.prepareStatement(sql);
        statement.setString(1, "博人");
        statement.setString(2, String.valueOf('男'));
        statement.setInt(3,18);
        statement.setString(4, "cui");

        //5.执行sql，获取结果集
        int i = statement.executeUpdate();
        System.out.println("向数据库中插入了i = " + i);

        //6. 关闭资源
        //关闭资源
        statement.close();
        connection.close();
    }
}
